/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.thienloc.omap.jdbc.dao;

import com.thienloc.omap.entity.report.MoStatistic;
import com.thienloc.omap.jdbc.entity.RawMo;
import com.thienloc.omap.util.DBUtil;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 *
 * @author GiangLT
 */
public class RawMoDao extends BaseDao {

    public List<RawMo> getRawMoList(Date startDate, Date endDate, List<String> serviceCodes, List<String> telcos) throws Exception {
        List<RawMo> result = new ArrayList<RawMo>();
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;

        try {
            Calendar originalStartTime = Calendar.getInstance();
            Calendar originalEndTime = Calendar.getInstance();
            originalStartTime.setTime(startDate);
            originalEndTime.setTime(endDate);

            Calendar startTime = originalStartTime;
            Calendar endTime = originalEndTime;

            int startMonth, endMonth, startYear, endYear;
            do {
                startMonth = startTime.get(Calendar.MONTH) + 1;
                startYear = startTime.get(Calendar.YEAR);

                Calendar inMonthStartTime = startTime;
                inMonthStartTime.set(Calendar.DATE, inMonthStartTime.getActualMinimum(Calendar.DATE));//dua ve ngay mung 1
                if (inMonthStartTime.before(originalStartTime)) {
                    inMonthStartTime = originalStartTime;
                }

                Calendar inMonthEndTime = startTime;
                inMonthEndTime.set(Calendar.DATE, inMonthEndTime.getActualMaximum(Calendar.DATE));//dua ve ngay cuoi thang
                if (inMonthEndTime.after(originalEndTime)) {
                    inMonthEndTime = originalEndTime;//neu ngay ket thuc ko nam trong thang hien tai
                }

                //tao cau sql
                String tableName = "TBL_RAW_MO_" + String.valueOf(startMonth) + String.valueOf(startYear);
                StringBuilder sql = new StringBuilder("select distinct(msisdn) from ");
                sql.append(tableName);
                sql.append(" t where (t.time between ? and ?) ");

                if (serviceCodes != null && !serviceCodes.isEmpty()) {
                    sql.append("and service_code in (");
                    for (int i = 0; i < serviceCodes.size(); i++) {
                        sql.append("?, ");
                    }

                    sql.delete(sql.lastIndexOf(", "), sql.length());
                    sql.append(") ");
                }

                if (telcos != null && !telcos.isEmpty()) {
                    sql.append("and telco in (");
                    for (int i = 0; i < telcos.size(); i++) {
                        sql.append("?, ");
                    }

                    sql.delete(sql.lastIndexOf(", "), sql.length());
                    sql.append(") ");
                }

                //compile statement
                conn = DBUtil.getConnection(TL_JNDI);
                ps = conn.prepareStatement(sql.toString());
                int i = 1;
                ps.setDate(i++, new java.sql.Date(inMonthStartTime.getTimeInMillis()));
                ps.setDate(i++, new java.sql.Date(inMonthEndTime.getTimeInMillis()));

                if (serviceCodes != null && !serviceCodes.isEmpty()) {
                    for (String serviceCode : serviceCodes) {
                        ps.setString(i++, serviceCode);
                    }
                }

                if (telcos != null && !telcos.isEmpty()) {
                    for (String telco : telcos) {
                        ps.setString(i++, telco);
                    }
                }

                rs = ps.executeQuery();
                while (rs.next()) {
                    RawMo rawMo = new RawMo();
                    rawMo.setId(rs.getLong(1));
                    rawMo.setMsisdn(rs.getString(2));
                    rawMo.setSc(rs.getString(3));
                    rawMo.setTime(rs.getDate(4));
                    rawMo.setContent(rs.getString(5));
                    rawMo.setTelco(rs.getString(6));
                    rawMo.setM(rs.getString(7));
                    rawMo.setServiceCode(rs.getString(8));
                    rawMo.setGen1(rs.getString(9));
                    rawMo.setGen2(rs.getString(10));
                    rawMo.setGen3(rs.getString(11));
                    rawMo.setGen4(rs.getString(12));
                    rawMo.setGen5(rs.getString(13));

                    result.add(rawMo);
                }

                //next loop
                startTime.add(Calendar.MONTH, 1);
                startMonth = startTime.get(Calendar.MONTH) + 1;
                endMonth = endTime.get(Calendar.MONTH) + 1;

                startYear = startTime.get(Calendar.YEAR);
                endYear = endTime.get(Calendar.YEAR);
            } while (startMonth < endMonth && startYear <= endYear);

        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

        return result;
    }

    public Set<String> getRawMoList(Date startDate, Date endDate, String dailyStartTime, String dailyEndTime, List<String> serviceCodes, List<String> telcos) throws Exception {
        Set<String> result = new TreeSet<String>();
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;

        try {
            String[] tokens = dailyStartTime.split(":");
            int dailyStartHour = Integer.parseInt(tokens[0]);
            int dailyStartMinute = Integer.parseInt(tokens[1]);
            int dailyStartSecond = Integer.parseInt(tokens[2]);

            tokens = dailyEndTime.split(":");
            int dailyEndHour = Integer.parseInt(tokens[0]);
            int dailyEndMinute = Integer.parseInt(tokens[1]);
            int dailyEndSecond = Integer.parseInt(tokens[2]);

            Calendar originalStartTime = Calendar.getInstance();
            Calendar originalEndTime = Calendar.getInstance();
            originalStartTime.setTime(startDate);
            originalEndTime.setTime(endDate);
            originalEndTime.set(Calendar.HOUR_OF_DAY, 23);
            originalEndTime.set(Calendar.MINUTE, 59);
            originalEndTime.set(Calendar.SECOND, 59);

            Calendar startTime = Calendar.getInstance();
            startTime.setTime(originalStartTime.getTime());
            startTime.set(Calendar.HOUR_OF_DAY, dailyStartHour);
            startTime.set(Calendar.MINUTE, dailyStartMinute);
            startTime.set(Calendar.SECOND, dailyStartSecond);
            Calendar endTime = Calendar.getInstance();
            endTime.setTime(originalStartTime.getTime());
            endTime.set(Calendar.HOUR_OF_DAY, dailyEndHour);
            endTime.set(Calendar.MINUTE, dailyEndMinute);
            endTime.set(Calendar.SECOND, dailyEndSecond);

            SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
            StringBuilder querySuffix = new StringBuilder();
            if (serviceCodes != null && !serviceCodes.isEmpty()) {
                querySuffix.append("and service_code in ('");
                for (int i = 0; i < serviceCodes.size(); i++) {
                    querySuffix.append(serviceCodes.get(i)).append("', '");
                }

                querySuffix.delete(querySuffix.lastIndexOf("', '"), querySuffix.length());
                querySuffix.append("') ");
            }

            if (telcos != null && !telcos.isEmpty()) {
                querySuffix.append("and telco in ('");
                for (int i = 0; i < telcos.size(); i++) {
                    querySuffix.append(telcos.get(i)).append("', '");
                }

                querySuffix.delete(querySuffix.lastIndexOf("', '"), querySuffix.length());
                querySuffix.append("') ");
            }

            SimpleDateFormat tableNameDateFormat = new SimpleDateFormat("MMyyyy");
            StringBuilder sql = new StringBuilder();
            while (!startTime.after(originalEndTime)) {
                String tableName = "TBL_RAW_MO_" + tableNameDateFormat.format(startTime.getTime());
                sql.append("select distinct(msisdn) from ");
                sql.append(tableName);
                sql.append(" t where (t.time between to_date('");
                sql.append(dateFormat.format(startTime.getTime()));
                sql.append("', 'DD-MM-YYYY HH24:MI:SS') and to_date('");
                sql.append(dateFormat.format(endTime.getTime()));
                sql.append("', 'DD-MM-YYYY HH24:MI:SS')) ");
                sql.append(querySuffix);
                sql.append("union all ");

                //next loop
                startTime.add(Calendar.DATE, 1);
                endTime.add(Calendar.DATE, 1);
            }

            sql.delete(sql.lastIndexOf("union all "), sql.length());
            //compile statement
            conn = DBUtil.getConnection(TL_JNDI);
            ps = conn.prepareStatement(sql.toString());

            rs = ps.executeQuery();
            while (rs.next()) {
                result.add(rs.getString(1));
            }
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

        return result;
    }

    public List<MoStatistic> findByContentList(List<String> contents, List<String> shortCodes, List<String> dates) throws SQLException {
        List<MoStatistic> result = new ArrayList<MoStatistic>();
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;

        try {
            StringBuilder queryPrefix = new StringBuilder("SELECT m.sc, m.content, COUNT(m.content) AS total FROM ");
            StringBuilder querySuffix = new StringBuilder();
            querySuffix.append(" AND  m.content IN (");
            for (String content : contents) {
                querySuffix.append("'").append(content).append("',");
            }
            querySuffix.deleteCharAt(querySuffix.length() - 1);

            querySuffix.append(") AND  m.sc IN (");
            for (String shortCode : shortCodes) {
                querySuffix.append("'").append(shortCode).append("',");
            }
            querySuffix.deleteCharAt(querySuffix.length() - 1);
            querySuffix.append(") GROUP BY  m.sc, m.content ");


            StringBuilder sb = new StringBuilder();
            for (String date : dates) {
                String[] tokens = date.split("/");
                String tableName = "TBL_RAW_MO_";
                if (Integer.parseInt(tokens[1]) < 10) {
                    tableName += "0" + Integer.parseInt(tokens[1])  + tokens[2];
                } else {
                    tableName += Integer.parseInt(tokens[1])  + tokens[2];
                }

                sb.append(queryPrefix);
                sb.append(tableName);
                sb.append(" m WHERE TRUNC(m.time) = ");
                sb.append("'").append(date).append("' ");
                sb.append(querySuffix);

                sb.append(" union all ");
            }

            sb.delete(sb.lastIndexOf(" union all "), sb.length());

            conn = DBUtil.getConnection(TL_JNDI);
            ps = conn.prepareStatement(sb.toString());

            rs = ps.executeQuery();
            while (rs.next()) {
                MoStatistic moStatistic = new MoStatistic();
                moStatistic.setShortCode(rs.getString(1));
                moStatistic.setContent(rs.getString(2));
                moStatistic.setCount(rs.getBigDecimal(3));

                result.add(moStatistic);
            }

            for (int i = 0; i < result.size(); i++) {
                MoStatistic itemI = result.get(i);
                for (int j = 0; j < result.size(); j++) {
                    MoStatistic itemJ = result.get(j);
                    if (itemI != itemJ) {
                        if (itemI.getShortCode().equals(itemJ.getShortCode()) 
                                && itemI.getContent().equals(itemJ.getContent())) {
                            itemI.setCount(itemI.getCount().add(itemJ.getCount()));
                            result.remove(j);
                        }
                    }
                }

            }
        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

        return result;
    }

    public Set<String> findSubscriberList(String content, String shortCode, List<String> dates) throws SQLException {
        Set<String> result = new TreeSet<String>();
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;

        try {
            StringBuilder queryPrefix = new StringBuilder("SELECT DISTINCT(m.msisdn) FROM ");
            StringBuilder querySuffix = new StringBuilder();
            querySuffix.append(" AND  m.content = ");
            querySuffix.append("'").append(content).append("'");

            querySuffix.append(" AND  m.sc = ");
            querySuffix.append("'").append(shortCode).append("' ");

            StringBuilder sb = new StringBuilder();
            for (String date : dates) {
                String[] tokens = date.split("/");
                String tableName = "TBL_RAW_MO_";
                if (Integer.parseInt(tokens[1]) < 10) {
                    tableName += "0" + Integer.parseInt(tokens[1]) + tokens[2];
                } else {
                    tableName += Integer.parseInt(tokens[1]) + tokens[2];
                }

                sb.append(queryPrefix);
                sb.append(tableName);
                sb.append(" m WHERE TRUNC(m.time) = ");
                sb.append("'").append(date).append("' ");
                sb.append(querySuffix);

                sb.append(" union all ");
            }

            sb.delete(sb.lastIndexOf(" union all "), sb.length());

            conn = DBUtil.getConnection(TL_JNDI);
            ps = conn.prepareStatement(sb.toString());

            rs = ps.executeQuery();

            while (rs.next()) {
                result.add(rs.getString(1));
            }


        } finally {
            DbAccess.closeResultSet(rs);
            DbAccess.closePreparedStatement(ps);
            DbAccess.closeConn(conn);
        }

        return result;
    }
}
